import mysql.connector
import sqlite3 as sql
import pandas as pd
import numpy as np
import matplotlib as mpl
import matplotlib.pyplot as plt
import seaborn as sn
data = pd.read_csv(r"C:\Users\User\Downloads\casestudy.csv")
data.head()
| Index | Customer_email | net_revenue | year | |
|---|---|---|---|---|
| 0 | 685926 | aregboumbw@gmail.com | 208.43 | 2017 |
| 1 | 685925 | wvkpmwsgck@gmail.com | 235.35 | 2017 |
| 2 | 685924 | appaplmgko@gmail.com | 200.98 | 2017 |
| 3 | 685923 | pjodiifjop@gmail.com | 133.03 | 2017 |
| 4 | 685922 | qzqttwiftu@gmail.com | 184.58 | 2017 |
data.isnull()
| Index | Customer_email | net_revenue | year | |
|---|---|---|---|---|
| 0 | False | False | False | False |
| 1 | False | False | False | False |
| 2 | False | False | False | False |
| 3 | False | False | False | False |
| 4 | False | False | False | False |
| ... | ... | ... | ... | ... |
| 685922 | False | False | False | False |
| 685923 | False | False | False | False |
| 685924 | False | False | False | False |
| 685925 | False | False | False | False |
| 685926 | False | False | False | False |
685927 rows × 4 columns
import mysql.connector as msql
from mysql.connector import Error
try:
conn = msql.connect(host='localhost', user='root', password='kaamya')
if conn.is_connected():
cursor = conn.cursor()
cursor.execute("CREATE DATABASE scout_DB")
print("scout_DB database is created")
except Error as e:
print("Error while connecting to MySQL", e)
Error while connecting to MySQL 1007 (HY000): Can't create database 'scout_db'; database exists
try:
conn = msql.connect(host='localhost', database='scout_DB', user='root',password='kaamya')
if conn.is_connected():
cursor = conn.cursor()
cursor.execute("select database();")
record = cursor.fetchone()
print("You're connected to database: ", record)
cursor.execute('DROP TABLE IF EXISTS iris;')
print('Creating table....')
cursor.execute("CREATE TABLE customer_data_105 (indexes INT PRIMARY KEY, customer_email char(50) , net_revenue FLOAT, years int)")
print("customer table is created....")
for i,row in data.iterrows():
sql = "INSERT INTO scout_DB.customer_data_105 VALUES (%s,%s,%s,%s)"
cursor.execute(sql, tuple(row))
print("Record inserted")
# the connection is not autocommitted by default, so we must commit to save our changes
conn.commit()
except Error as e:
print("Error while connecting to MySQL", e)
You're connected to database: ('scout_db'